/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.techshop.dao.impl;

import com.techshop.dao.RevenueReportDao;
import java.util.List;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
 *
 * @author Hai-PC
 */
public class RevenueReportDaoImpl extends HibernateDaoSupport implements RevenueReportDao
{
    @Override
    public List<Object[]> getRevenueAndExpenditure(int month, int year)
    {
        try
        {
            String sql = "SELECT date, sum(A.TongThu), sum(A.TongChi), sum(A.TongThu) - sum(A.TongChi) as DoanhThu from " +
                            "(select date, sum(total) as TongThu, 0 as TongChi from tgdd7.order " +
                            "where state is true " +
                            "group by date(date) " +
                            "union " +
                            "select date, 0 as TongThu, sum(total) as TongChi from tgdd7.receipt_note " +
                            "group by date(date)) A " +
                            "where month(date) = :month and year(date) = :year " +
                            "group by date(A.date)";
            List<Object[]> results = getSession().createSQLQuery(sql).setParameter("month", month).setParameter("year", year).list();
            return results;
        }
        catch (DataAccessResourceFailureException e)
        {
            System.err.printf("Error: ", e.toString());
            return null;
        }
    }
    
    @Override
    public List<Object[]> getRevenueAndExpenditureByStore(int month, int year)
    {
        try
        {
            String sql = "SELECT name, date, sum(A.TongThu), sum(A.TongChi), sum(A.TongThu) - sum(A.TongChi) as DoanhThu from " +
                            "(select s.name, date, sum(total) as TongThu, 0 as TongChi from tgdd7.order o, store s " +
                            "where state is true and o.store_id = s.id " +
                            "group by s.name, date(date) " +
                            "union " +
                            "select s.name, date, 0 as TongThu, sum(total) as TongChi from receipt_note r, store s " +
                            "where r.store_id = s.id " +
                            "group by s.name, date(date)) A " +
                            "where month(date) = :month and year(date) = :year " +
                            "group by A.name, date(A.date)";
            List<Object[]> results = getSession().createSQLQuery(sql).setParameter("month", month).setParameter("year", year).list();
            return results;
        }
        catch (DataAccessResourceFailureException e)
        {
            System.err.printf("Error: ", e.toString());
            return null;
        }
    }
    
    @Override
    public List<Object[]> getInventory(int month, int year)
    {
        try
        {
            String sql = "select SanPham, Mau, sum(TongNhap), sum(TongXuat) from " +
                            "(select date, p.name as SanPham, c.name as Mau, sum(rd.quantity) as TongNhap, 0 as TongXuat from tgdd7.receipt_note r, tgdd7.receipt_note_detail rd, phone p, color c " +
                            "where r.id = rd.receipt_note_id and p.id = rd.phone_id and c.id = rd.color_id " +
                            "group by rd.phone_id, rd.color_id " +
                            "union " +
                            "select date, p.name as SanPham, c.name as Mau, 0 as TongNhap, sum(od.quantity) as TongXuat from tgdd7.order o, tgdd7.order_detail od, phone p, color c, phone_code pc " +
                            "where o.id = od.order_id and pc.id = od.phone_code and c.id = pc.color_id and p.id = pc.phone_id " +
                            "group by p.id, c.id) A " +
                            "where month(A.date) = :month and year(A.date) = :year " +
                            "group by SanPham, Mau";
            List<Object[]> results = getSession().createSQLQuery(sql).setParameter("month", month).setParameter("year", year).list();
            return results;
        }
        catch (DataAccessResourceFailureException e)
        {
            System.err.printf("Error: ", e.toString());
            return null;
        }
    
    }
    @Override
    public List<Object[]> getInventoryByStore(int month, int year)
    {
        try
        {
            String sql = "select name, SanPham, Mau, sum(TongNhap), sum(TongXuat) from " +
                            "(select s.name, date, p.name as SanPham, c.name as Mau, sum(rd.quantity) as TongNhap, 0 as TongXuat from tgdd7.receipt_note r, tgdd7.receipt_note_detail rd, phone p, color c, store s " +
                            "where r.id = rd.receipt_note_id and p.id = rd.phone_id and c.id = rd.color_id and r.store_id = s.id " +
                            "group by s.name, rd.phone_id, rd.color_id " +
                            "union " +
                            "select s.name, date, p.name as SanPham, c.name as Mau, 0 as TongNhap, sum(od.quantity) as TongXuat from tgdd7.order o, tgdd7.order_detail od, phone p, color c, phone_code pc, store s " +
                            "where o.id = od.order_id and pc.id = od.phone_code and c.id = pc.color_id and p.id = pc.phone_id and o.store_id = s.id " +
                            "group by s.name, p.id, c.id) A " +
                            "where month(A.date) = :month and year(A.date) = :year " +
                            "group by A.name, A.SanPham, A.Mau";
            List<Object[]> results = getSession().createSQLQuery(sql).setParameter("month", month).setParameter("year", year).list();
            return results;
        }
        catch (DataAccessResourceFailureException e)
        {
            System.err.printf("Error: ", e.toString());
            return null;
        }
    }
    
    @Override
    public List<Object[]> getInventoryPhone(int month, int year)
    {
        try
        {
            String sql = "select SanPham, sum(TongNhap), sum(TongXuat) from " +
                            "(select date, p.name as SanPham, c.name as Mau, sum(rd.quantity) as TongNhap, 0 as TongXuat from tgdd7.receipt_note r, tgdd7.receipt_note_detail rd, phone p, color c " +
                            "where r.id = rd.receipt_note_id and p.id = rd.phone_id and c.id = rd.color_id " +
                            "group by rd.phone_id, rd.color_id " +
                            "union " +
                            "select date, p.name as SanPham, c.name as Mau, 0 as TongNhap, sum(od.quantity) as TongXuat from tgdd7.order o, tgdd7.order_detail od, phone p, color c, phone_code pc " +
                            "where o.id = od.order_id and pc.id = od.phone_code and c.id = pc.color_id and p.id = pc.phone_id " +
                            "group by p.id, c.id) A " +
                            "where month(A.date) = :month and year(A.date) = :year " +
                            "group by SanPham";
            List<Object[]> results = getSession().createSQLQuery(sql).setParameter("month", month).setParameter("year", year).list();
            return results;
        }
        catch (DataAccessResourceFailureException e)
        {
            System.err.printf("Error: ", e.toString());
            return null;
        }
    }
}